/**
 * 
 */
package org.swing.utility.jdbc.helper;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author lqnhu
 *
 */
public final class DbUtils {
	/**
	 * Close a <code>Connection</code>, avoid closing if null.
	 *
	 * @param conn
	 *            Connection to close.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public static void close(Connection conn) throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * Close a <code>ResultSet</code>, avoid closing if null.
	 *
	 * @param rs
	 *            ResultSet to close.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public static void close(ResultSet rs) throws SQLException {
		if (rs != null) {
			rs.close();
		}
	}

	/**
	 * Close a <code>Statement</code>, avoid closing if null.
	 *
	 * @param stmt
	 *            Statement to close.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public static void close(Statement stmt) throws SQLException {
		if (stmt != null) {
			stmt.close();
		}
	}

	/**
	 * Close a <code>Connection</code>, avoid closing if null and hide any
	 * SQLExceptions that occur.
	 *
	 * @param conn
	 *            Connection to close.
	 */
	public static void closeQuietly(Connection conn) {
		try {
			close(conn);
		} catch (SQLException e) {
			// quiet
		}
	}

	/**
	 * Close a <code>Connection</code>, <code>Statement</code> and
	 * <code>ResultSet</code>. Avoid closing if null and hide any SQLExceptions
	 * that occur.
	 *
	 * @param conn
	 *            Connection to close.
	 * @param stmt
	 *            Statement to close.
	 * @param rs
	 *            ResultSet to close.
	 */
	public static void closeQuietly(Connection conn, Statement stmt,
			ResultSet rs) {
		try {
			closeQuietly(rs);
		} finally {
			try {
				closeQuietly(stmt);
			} finally {
				closeQuietly(conn);
			}
		}
	}

	/**
	 * Close a <code>ResultSet</code>, avoid closing if null and hide any
	 * SQLExceptions that occur.
	 *
	 * @param rs
	 *            ResultSet to close.
	 */
	public static void closeQuietly(ResultSet rs) {
		try {
			close(rs);
		} catch (SQLException e) {
			// quiet
		}
	}

	/**
	 * Close a <code>Statement</code>, avoid closing if null and hide any
	 * SQLExceptions that occur.
	 *
	 * @param stmt
	 *            Statement to close.
	 */
	public static void closeQuietly(Statement stmt) {
		try {
			close(stmt);
		} catch (SQLException e) {
			// quiet
		}
	}

	/**
	 * Commits a <code>Connection</code> then closes it, avoid closing if null.
	 *
	 * @param conn
	 *            Connection to close.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public static void commitAndClose(Connection conn) throws SQLException {
		if (conn != null) {
			try {
				conn.commit();
			} finally {
				conn.close();
			}
		}
	}

	/**
	 * Commits a <code>Connection</code> then closes it, avoid closing if null
	 * and hide any SQLExceptions that occur.
	 *
	 * @param conn
	 *            Connection to close.
	 */
	public static void commitAndCloseQuietly(Connection conn) {
		try {
			commitAndClose(conn);
		} catch (SQLException e) {
			// quiet
		}
	}

	/**
	 * Loads and registers a database driver class. If this succeeds, it returns
	 * true, else it returns false.
	 *
	 * @param driverClassName
	 *            of driver to load
	 * @return boolean <code>true</code> if the driver was found, otherwise
	 *         <code>false</code>
	 */
	public static boolean loadDriver(String driverClassName) {
		try {
			Class.forName(driverClassName).newInstance();
			return true;
		} catch (ClassNotFoundException e) {
			return false;
		} catch (IllegalAccessException e) {
			// Constructor is private, OK for DriverManager contract
			return true;
		} catch (InstantiationException e) {
			return false;
		} catch (Throwable e) {
			return false;
		}
	}

	/**
	 * Print the stack trace for a SQLException to STDERR.
	 *
	 * @param e
	 *            SQLException to print stack trace of
	 */
	public static void printStackTrace(SQLException e) {
		printStackTrace(e, new PrintWriter(System.err));
	}

	/**
	 * Print the stack trace for a SQLException to a specified PrintWriter.
	 *
	 * @param e
	 *            SQLException to print stack trace of
	 * @param pw
	 *            PrintWriter to print to
	 */
	public static void printStackTrace(SQLException e, PrintWriter pw) {
		SQLException next = e;
		while (next != null) {
			next.printStackTrace(pw);
			next = next.getNextException();
			if (next != null) {
				pw.println("Next SQLException:");
			}
		}
	}

	/**
	 * Print warnings on a Connection to STDERR.
	 *
	 * @param conn
	 *            Connection to print warnings from
	 */
	public static void printWarnings(Connection conn) {
		printWarnings(conn, new PrintWriter(System.err));
	}

	/**
	 * Print warnings on a Connection to a specified PrintWriter.
	 *
	 * @param conn
	 *            Connection to print warnings from
	 * @param pw
	 *            PrintWriter to print to
	 */
	public static void printWarnings(Connection conn, PrintWriter pw) {
		if (conn != null) {
			try {
				printStackTrace(conn.getWarnings(), pw);
			} catch (SQLException e) {
				printStackTrace(e, pw);
			}
		}
	}

	/**
	 * Rollback any changes made on the given connection.
	 * 
	 * @param conn
	 *            Connection to rollback. A null value is legal.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public static void rollback(Connection conn) throws SQLException {
		if (conn != null) {
			conn.rollback();
		}
	}

	/**
	 * Performs a rollback on the <code>Connection</code> then closes it, avoid
	 * closing if null.
	 *
	 * @param conn
	 *            Connection to rollback. A null value is legal.
	 * @throws SQLException
	 *             if a database access error occurs
	 * @since DbUtils 1.1
	 */
	public static void rollbackAndClose(Connection conn) throws SQLException {
		if (conn != null) {
			try {
				conn.rollback();
			} finally {
				conn.close();
			}
		}
	}

	/**
	 * Performs a rollback on the <code>Connection</code> then closes it, avoid
	 * closing if null and hide any SQLExceptions that occur.
	 *
	 * @param conn
	 *            Connection to rollback. A null value is legal.
	 * @since DbUtils 1.1
	 */
	public static void rollbackAndCloseQuietly(Connection conn) {
		try {
			rollbackAndClose(conn);
		} catch (SQLException e) {
			// quiet
		}
	}
}
